package com.anhry.app.safety.manage.oa.service.impl;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.anhry.app.safety.manage.enterprise.bean.TbCorpPerson;
import com.anhry.app.safety.manage.oa.bean.TbOaMail;
import com.anhry.app.safety.manage.oa.bean.TbOaMailUser;
import com.anhry.app.safety.manage.oa.bean.TbOaMailView;
import com.anhry.app.safety.manage.oa.bean.TbOaNoticeUser;
import com.anhry.app.safety.manage.oa.service.MailService;
import com.anhry.app.safety.manage.system.bean.TbUser;
import com.anhry.app.safety.util.Constants;
import com.anhry.app.safety.util.dao.UtilDao;
/**
 * @author chen
 */
@Service
public class MailServiceImpl implements MailService {

	private static Logger logger = Logger.getLogger(MailServiceImpl.class);

	@Resource
	private UtilDao utilDao;

	public UtilDao getUtilDao() {
		return utilDao;
	}

	public List<TbUser> loadUserList(String userGrade, String userUnit,
			String userDept, String userZws, String keyWord, int startRow,
			int size) throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbUser as user left join fetch user.tbUserDept left join fetch user.tbUserUnit where user.userRealName like '%"
						+ keyWord
						+ "%' and user.isDele=0 and user.tbUserRole.id<>'1'");
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbUser as user where user.userRealName like '%"
							+ keyWord
							+ "%' and user.isDele=0 and user.tbUserRole.id<>'1'");
		}
		if (null != userGrade && !"".equals(userGrade)) {
			if (null != userUnit && !"".equals(userUnit)) {
				if (Constants.USER_GRADE_ID_OF_CITY_INDUSTRY.equals(userGrade)) {// 行业用户
					sbf.append(" and user.tbUserUnit.id="
							+ Long.parseLong(userUnit) + "");
				} else {
					if (null != userDept && !"".equals(userDept)) {
						sbf.append(" and user.tbUserDept.id="
								+ Long.parseLong(userDept) + "");
					} else {
						sbf.append(" and user.tbUserUnit.id="
								+ Long.parseLong(userUnit)
								+ " and user.tbUserGrade.id='" + userGrade
								+ "'");
					}
				}
			} else {
				sbf.append(" and user.tbUserGrade.id='" + userGrade + "'");
			}
		}
		if (null != userZws && userZws.length() > 0) {
			sbf.append(" and user.userZw='" + userZws + "'");
		}
		sbf.append(" order by user.id");

		return (List) utilDao.getHqlQuery(sbf.toString(), startRow, size);
	}

	public int loadMailList(String keyWord, String beginTime, String endTime,
			TbUser user, String isDele, String isRead) throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		int size = 0;
		Integer tt = 0;
		List list = null;
		Session s = utilDao.getHibernateSession();
		try {
			StringBuffer sbf = new StringBuffer(
					"select count(*) from tb_oa_mail_user mu,tb_oa_mail mail where mail.mail_title like '%"
							+ keyWord
							+ "%' and mu.is_dele='"
							+ isDele
							+ "' and mu.is_drop=0 and mu.addressee_id='"
							+ user.getId() + "'");
			if (null != beginTime && !"".equals(beginTime)) {
				sbf
						.append(" and to_char(mail.mail_time,'yyyy-mm-dd') >= '"
								+ beginTime + "'");
			}
			if (null != endTime && !"".equals(endTime)) {
				sbf
						.append(" and to_char(mail.mail_time,'yyyy-mm-dd') <= '"
								+ endTime + "'");
			}
			if (null != isRead && !"".equals(isRead)) {
				sbf.append(" and mu.is_read='" + isRead + "'");
			}
			sbf.append(" and mu.mail_id=mail.id");
			SQLQuery query = s.createSQLQuery(sbf.toString());
			list = query.list();
			if (list != null && list.size() > 0) {
				Object temp = (Object) list.get(0);
				if (temp != null && !"".equals(temp.toString())) {
					tt = Integer.valueOf(temp.toString());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}
		if (tt != null) {
			size = tt.intValue();
		}
		return size;
	}

	public List<TbOaMailUser> loadMailList(String keyWord, String beginTime,
			String endTime, TbUser user, String isDele, String isRead,
			int startRow, int size) throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaMailUser mu left join fetch mu.tbOaMail where mu.tbOaMail.mailTitle like '%"
						+ keyWord
						+ "%' and mu.isDele='"
						+ isDele
						+ "' and mu.isDrop=0 and mu.addresseeId.id='"
						+ user.getId() + "'");
		if (null != beginTime && !"".equals(beginTime)) {
			sbf
					.append(" and to_char(mu.tbOaMail.mailTime,'yyyy-mm-dd') >= '"
							+ beginTime + "'");
		}
		if (null != endTime && !"".equals(endTime)) {
			sbf
					.append(" and to_char(mu.tbOaMail.mailTime,'yyyy-mm-dd') <= '"
							+ endTime + "'");
		}
		if (null != isRead && !"".equals(isRead)) {
			sbf.append(" and mu.isRead='" + isRead + "'");
		}
		sbf.append(" order by mu.tbOaMail.mailTime desc");
		return (List) utilDao.getHqlQuery(sbf.toString(), startRow, size);
	}

	/*
	 * 需短信通知的邮件列表
	 */
	public List<TbOaMailUser> getNoteMailList() throws Exception {
		StringBuffer sbf = new StringBuffer(
				"from TbOaMailUser mail left join fetch mail.tbOaMail    left join fetch mail.tbOaMail.tbUser.tbUserUnit   left join fetch mail.tbOaMail.tbUser.tbUserDept    where mail.tbOaMail.isNote = 1 and mail.isDele=0");
		sbf.append(" and to_char(mail.tbOaMail.mailTime+mail.tbOaMail.noteDay+mail.tbOaMail.noteHour/24+mail.tbOaMail.noteSec/24/60,'yyyy-MM-dd HH:mm') = to_char(sysdate,'yyyy-MM-dd HH:mm')");
		sbf.append(" order by mail.tbOaMail.mailTime asc");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List<TbOaMailUser> list = new ArrayList<TbOaMailUser>();
		try {
			Query query = null;
			query = s.createQuery(sql);
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}
		this.getPhone(list);
		return list;
		}
	
//	打印短信提醒信息和对应号码
	public void getPhone(List<TbOaMailUser> list){
		for(TbOaMailUser mail : list){
			System.out.println(mail.getTbOaMail().getNoteContent());
			if(mail.getAddresseeId().getUserPhone()!= null && mail.getAddresseeId().getUserPhone().length() > 0){
				System.out.println(mail.getAddresseeId().getUserPhone());
			}else{
				logger.debug("MailServiceImpl:getPhone没填写手机号码");
				System.out.println("没填写完整信息");
			}
			if(mail.getTbOaMail().getTbUser() != null){
			  if(mail.getTbOaMail().getTbUser().getUserPhone()!=null && mail.getTbOaMail().getTbUser().getUserPhone().length()>0){
				System.out.println(mail.getTbOaMail().getTbUser().getUserPhone());
			  }else{
				logger.debug("MailServiceImpl:getPHone没填写手机号码");
				System.out.println("没填写完整信息");
			  }
		  }else{
			  logger.debug("MailServiceImpl:getPHone保存邮件时有问题");
			  System.out.println("保存邮件时有问题");
		  }
		  }
		}
	
	public int loadSendMailList(String keyWord, String beginTime,
			String endTime, TbUser user, String isDele) throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		int size = 0;
		Integer tt = 0;
		List list = null;
		Session s = utilDao.getHibernateSession();
		try {
			StringBuffer sbf = new StringBuffer(
					"select count(*) from tb_oa_mail mail where mail.mail_title like '%"
							+ keyWord + "%' and mail.is_dele='" + isDele
							+ "' and mail.is_drop=0 and mail.user_id='"
							+ user.getId() + "' and mail.is_submit=0");
			if (null != beginTime && !"".equals(beginTime)) {
				sbf
						.append(" and to_char(mail.mail_time,'yyyy-mm-dd') >= '"
								+ beginTime + "'");
			}
			if (null != endTime && !"".equals(endTime)) {
				sbf
						.append(" and to_char(mail.mail_time,'yyyy-mm-dd') <= '"
								+ endTime + "'");
			}
			SQLQuery query = s.createSQLQuery(sbf.toString());
			list = query.list();
			if (list != null && list.size() > 0) {
				Object temp = (Object) list.get(0);
				if (temp != null && !"".equals(temp.toString())) {
					tt = Integer.valueOf(temp.toString());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}
		if (tt != null) {
			size = tt.intValue();
		}
		return size;
	}

	public List<TbOaMail> loadSendMailList(String keyWord, String beginTime,
			String endTime, TbUser user, String isDele, int startRow, int size)
			throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaMail mail left join fetch mail.tbUser where mail.mailTitle like '%"
						+ keyWord + "%' and mail.isDele='" + isDele
						+ "' and mail.isDrop=0 and mail.tbUser.id='"
						+ user.getId() + "' and mail.isSubmit=0");
		if (null != beginTime && !"".equals(beginTime)) {
			sbf.append(" and to_char(mail.mailTime,'yyyy-mm-dd') >= '"
					+ beginTime + "'");
		}
		if (null != endTime && !"".equals(endTime)) {
			sbf.append(" and to_char(mail.mailTime,'yyyy-mm-dd') <= '"
				    + endTime + "'");
		}
		sbf.append(" order by mail.mailTime desc");

		return (List) utilDao.getHqlQuery(sbf.toString(), startRow, size);
	}

	public List<TbOaMailView> loadMailDeleList(TbUser user, int startRow,
			int size) throws Exception {
		StringBuffer sbf = new StringBuffer(
				"from TbOaMailView mail where mail.isDele=1 and mail.isDrop=0 and mail.mailUser='"
						+ user.getId() + "'");
		sbf.append(" order by mail.mailTime desc");
		return (List) utilDao.getHqlQuery(sbf.toString(), startRow, size);
	}

	
}
